package com.apache.log.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.apache.cache.service.impl.LoadCacheFactory;
import com.apache.exception.BusinessException;
import com.apache.tools.DataMap;
import com.apache.tools.MD5Utils;
import com.apache.tools.StrUtil;
import com.apache.uct.common.ToolsUtil;
import net.sf.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @description: 对数据库的直接操作类
 * @author: Hou Dayu
 * @date: Created in 2020/10/28
 */
public class DatabaseOperate {
    private Logger log = LoggerFactory.getLogger("db logs");

    private static DatabaseOperate instance = new DatabaseOperate();
    private Map<String, DruidDataSource> dbMap = new ConcurrentHashMap();
    private DatabaseOperate(){
        ///构造私有化，不允许被new
    }

    public static DatabaseOperate getInstance(){
        return instance;
    }

    /**
     * 执行sql的增，删，改
     */
    public boolean execute(String datasourceKey, String ...sqls){
        DruidDataSource dataSource = getDataSource(datasourceKey);
        return exeSql(datasourceKey,dataSource,sqls);
    }

    /**
     * 执行sql的增，删，改
     */
    public boolean execute(String url, String driverClassName, String user, String password, String ...sqls){
        String datasourceKey = MD5Utils.MD5(url+user);
        DruidDataSource dataSource = getDataSource(datasourceKey, url,driverClassName,user,password);
        return exeSql(datasourceKey,dataSource,sqls);
    }

    /**
     * 执行sql的分页查询
     */
    public Map<String,Object> selectPage(String datasourceKey, String sql,String orderByStr,int pageIndex,int pageSize){
        DruidDataSource dataSource = getDataSource(datasourceKey);
        return getPage(datasourceKey,dataSource,sql,orderByStr,pageIndex,pageSize);
    }

    /**
     * 执行sql的分页查询
     */
    public Map<String,Object> selectPage(String url, String driverClassName, String user, String password, String sql,String orderByStr,int pageIndex,int pageSize){
        String datasourceKey = MD5Utils.MD5(url+user);
        DruidDataSource dataSource = getDataSource(datasourceKey, url,driverClassName,user,password);
        return getPage(datasourceKey,dataSource,sql,orderByStr,pageIndex,pageSize);
    }

    public DataMap<String> selectInfo(String datasourceKey, String sql){
        DruidDataSource dataSource = getDataSource(datasourceKey);
        try {
            List<Map> list = select(dataSource.getConnection(),sql);
            if(!StrUtil.isEmpty(list)) {
                return (DataMap<String>) list.get(0);
            }
        } catch (SQLException e){
            dataSource.close();
            dbMap.remove(datasourceKey);
            throw new BusinessException("执行sql异常:" + e.getMessage());
        }
        return null;
    }

    /**
     * 执行sql的增，删，改
     */
    private boolean exeSql(String datasourceKey, DruidDataSource dataSource, String ...sqls){
        Statement stmt = null;
        Connection conn = null;
        boolean v = false;
        try {
            conn = dataSource.getConnection();
            if (sqls.length > 1) {
                stmt = conn.createStatement();
                for(String sql : sqls){
                    stmt.addBatch(sql);
                }
                stmt.executeBatch();
                return true;
            } else {
                conn.setAutoCommit(true);
                stmt = conn.createStatement();
                v = stmt.execute(sqls[0]);
            }
        } catch (SQLException e){
            log.error("执行sql:(" + sqls + ")出错；异常信息：" + e.getMessage());
            dataSource.close();
            dbMap.remove(datasourceKey);
            throw new BusinessException("执行sql异常:" + e.getMessage());
        } finally {
            close(conn, stmt, null);
        }
        return v;
    }

    private Map<String,Object> getPage(String datasourceKey,DruidDataSource dataSource, String sql,String orderBy,int pageIndex,int pageSize){
        String dbType = getDataBaseType(dataSource.getDriverClassName());
        StringBuilder strBuf = new StringBuilder();
        String orderByStr = "";
        if (ToolsUtil.isNotNull(orderBy)) {
            orderByStr = " order by " + orderBy;
        }
        if(pageIndex >0 && pageSize>0) {
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageSize * pageIndex;
            if ("oracle".equalsIgnoreCase(dbType)) {
                strBuf.append("SELECT * FROM (SELECT A.*, ROWNUM RN FROM (");
                strBuf.append("select * from (").append(sql).append(orderByStr).append(") Expr1");
                strBuf.append(" )A WHERE ROWNUM <=" + end);
                strBuf.append(")WHERE RN >=" + start);
            } else if ("mySql".equalsIgnoreCase(dbType)) {
                start = start - 1;
                if (start > 0) {
                    end = end - start;
                    strBuf.append("SELECT * FROM (" + sql + ") t " + orderByStr + " limit " + start + "," + end);
                } else {
                    strBuf.append("SELECT * FROM (" + sql + ") t " + orderByStr + " limit " + end);
                }
            } else {
                strBuf.append(sql).append(orderByStr);
            }
        } else {
            strBuf.append(sql + orderByStr);
        }
        Map<String,Object> data = new HashMap();
        try {
            if(pageIndex >0 && pageSize>0) {
                int total = selectCount(dataSource.getConnection(), "select count(*) from (" + sql + ") t");
                data.put("total", total);
            }
            List list = select(dataSource.getConnection(),strBuf.toString());
            data.put("rows",list);
        } catch (SQLException e){
            dataSource.close();
            dbMap.remove(datasourceKey);
            throw new BusinessException("执行sql异常:" + e.getMessage());
        }
        return data;
    }

    /**
     * 获取总记录条数
     * @param conn
     * @param sql
     * @return
     */
    private int selectCount(Connection conn, String sql) throws SQLException{
        ResultSet rs = null;
        Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
        int count = 0;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } finally {
            close(conn, stmt, rs);
        }
        return count;
    }

    private List select(Connection conn, String sql) throws SQLException {
        ResultSet rs = null;
        Statement stmt = null;//stmt用于发送sql语句到数据库并执行sql语句
        List<Map> list = new ArrayList();
        log.info("待查询sql={}",sql);
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            while (rs.next()) {
                DataMap<Object> fn = new DataMap<Object>();
                for (int i = 1; i <= count; i++) {
                    String key = rsmd.getColumnLabel(i);
                    String value = rs.getString(i);
                    fn.put(key, value);
                }
                list.add(fn);
            }
        } finally {
            close(conn, stmt, rs);
        }
        return list;
    }

    private DruidDataSource getDataSource(String datasourceKey){
        if(!dbMap.containsKey(datasourceKey)){
            Object obj = LoadCacheFactory.getInstance().getCacheManager("").getCacheObjectByKey("rpc_datasource_" + datasourceKey);
            if(null == obj)
                return null;
            JSONObject json = JSONObject.fromObject(obj);
            DruidDataSource dbDao = initDataSource(json.getString("jdbcUrl"),json.getString("driverClassName"),json.getString("jdbcUsername"),json.getString("jdbcPassword"));
            dbMap.put(datasourceKey,dbDao);
        }
        return dbMap.get(datasourceKey);
    }

    private DruidDataSource getDataSource(String datasourceKey, String url, String driverClassName, String user, String password){
        if(!dbMap.containsKey(datasourceKey)){
            DruidDataSource dbDao = initDataSource(url,driverClassName,user,password);
            dbMap.put(datasourceKey,dbDao);
        }
        return dbMap.get(datasourceKey);
    }

    /**
     * 实例数据库连接池
     */
    private DruidDataSource initDataSource(String url, String name, String user, String password) {
        DruidDataSource dsss = new DruidDataSource();
        dsss.setDriverClassName(name);
        dsss.setUrl(url);
        dsss.setUsername(user);
        dsss.setPassword(password);
        dsss.setInitialSize(3);
        dsss.setMaxActive(10);
        dsss.setMinIdle(3);
        dsss.setMaxWait(60000);//最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间
        dsss.setRemoveAbandonedTimeout(180);//超过时间限制，回收没有用(废弃)的连接
        dsss.setRemoveAbandoned(true);//超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收
        dsss.setPoolPreparedStatements(true);//是否预编译SQL语句
        return dsss;
    }

    /**
     * 获取数据库类型
     * @param driverClassName 数据库驱动
     */
    public String getDataBaseType(String driverClassName) {
        if ("oracle.jdbc.OracleDriver".equals(driverClassName)) {
            return "oracle";
        } else if ("com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driverClassName)) {
            return "sqlserver";
        } else {
            return "mysql";
        }
    }

    public void close(Connection con, Statement stmt, ResultSet rs) {
        try {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
        } catch (SQLException e) {
            log.error("关闭连接出错；异常信息：{}",e.getMessage());
        } finally {
            try {
                if (null != con)
                    con.close();
            } catch (SQLException e) {
            }
        }
    }
}
